package sv.com.seguros.dao.asg;

import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.Hibernate;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.hibernate.type.Type;

import sv.com.seguros.dao.BaseDaoImpl;
import sv.com.seguros.dto.asg.AsgPendiente;
import sv.com.seguros.exception.WebException;

public class AsgPendienteDaoImpl extends BaseDaoImpl implements AsgPendienteDao {

	@SuppressWarnings("unchecked")
	public List<Object[]> findAllByAsgPolizaId(Long asgPolizaId) throws WebException {
		try {
			DetachedCriteria criteria = DetachedCriteria.forClass(AsgPendiente.class);
			criteria.createAlias("asgPoliza", "asgPoliza", Criteria.INNER_JOIN);
			criteria.setProjection(Projections.projectionList().
				add(Projections.property("asgPendienteId")).
				add(Projections.property("asgPendienteNumero")).
				add(Projections.property("asgPendienteResponsable")).
				add(Projections.property("asgPendienteFecha")).
				add(Projections.property("asgPendienteAsunto")).
				add(Projections.property("asgPendienteFinalizado")).
				add(Projections.property("asgPendienteFechaFinalizacion")).
				add(Projections.sqlProjection(
						"CASE " +
					    "    WHEN asg_pen_fecha_finalizado IS NOT NULL THEN " + 
					    "        TO_DATE(SUBSTRING(asg_pen_fecha_finalizado, 1, 4) || '-' || SUBSTRING(asg_pen_fecha_finalizado, 5, 4), 'YYYY-MMDD') - " +
					    "        TO_DATE(SUBSTRING(asg_pen_fecha, 1, 4) || '-' || SUBSTRING(asg_pen_fecha, 5, 4), 'YYYY-MMDD') || ' DIA(S)' " +
					    "    ELSE '' " +
					    "END as asgPendienteTiempo", new String[]{"asgPendienteTiempo"}, new Type[]{Hibernate.STRING}))
			);
			criteria.add(Restrictions.eq("asgPoliza.asgPolizaId", asgPolizaId));
			criteria.addOrder(Order.asc("asgPendienteFinalizado"));
			criteria.addOrder(Order.desc("asgPendienteId"));
			return (List<Object[]>) findByCriteria(criteria);
		} catch (Exception e) {
			throw new WebException("Problemas tratar de obtener el detalle de pendientes");
		}
	}
		
}
